<?php
               
$path = realpath(dirname(__FILE__));                                  
require_once("config.php");
require_once("basedao.php");
require_once ("$path/../utils/mysqli/MysqliDb.php");



class Dispatcher {

    static $handlers = array();

    static function add($event, $handler)
    {
        self::$handlers[$event] = $handler;
    }

    static function run($event, $params)
    {
        $handlers = self::$handlers;
        if(array_key_exists($event, $handlers)) {
            $f = $handlers[$event];
            try{
                return call_user_func_array($f, $params);

            }catch(Exception $e){
                sendErrorResponse(-1,$e->getMessage()); 
                return ;
            }
        }

        return sendErrorResponse(-1,"Can't find action $event!"); 
    }

};



 

class VirtualDao extends BaseDao{

  
    function __construct(){
        parent::__construct();
    }
    
    function removeVirtual( $virtual ){
		log_message("remove vmpps(virtual)  $virtual    ");
        if( $virtual == null   )
        {
            throw new Exception('virtual   empty');
        }  

        $sql = "DELETE FROM DATA_VMMPP WHERE _VMETER=?  ";       
        $result = $this->db->rawQueryOne( $sql , array($virtual  ) ); 
        return $result ;
    }
    function removeMeter( $meter ){
		log_message("remove vmpps(meter)  $meter    ");
        if( $meter == null   )
        {
            throw new Exception('meter   empty');
        }  

        $sql = "DELETE FROM DATA_VMMPP WHERE _METER=?  ";       
        $result = $this->db->rawQueryOne( $sql , array($meter ) ); 
        return $result ;
    }

    function removeVmpp( $meter , $virtual  ){
		log_message("remove vmpp  $meter , $virtual   ");
        if( $meter == null || $virtual == null )
        {
            throw new Exception('meter or virtual empty');
        }  

        $sql = "DELETE FROM DATA_VMMPP WHERE _METER=? and _VMETER=?";       
        $result = $this->db->rawQueryOne( $sql , array($meter, $virtual) ); 
        return $result ;
    }

    function getMeters(   $virtual ){
        if(  $virtual == null )
        {
            throw new Exception(' virtual empty');
        }

        $sql = "SELECT m.* FROM DATA_VMMPP p, DATA_METER m WHERE m._METER=p._METER AND  _VMETER=?";      
        $result = $this->db->rawQuery ( $sql , array( $virtual)); 
        
        return $result ;
    }


    function getVmpp( $meter , $virtual ){
        if( $meter == null || $virtual == null )
        {
            throw new Exception('meter or virtual empty');
        }

        $sql = "SELECT * FROM DATA_VMMPP WHERE _METER=? and _VMETER=?";      
        $result = $this->db->rawQueryOne( $sql , array($meter, $virtual)); 
        return $result ;
    }

    function addVmpp( $meter , $virtual ){
		log_message("add vmpp  $meter , $virtual   ");
        $sql = "INSERT INTO DATA_VMMPP( _METER , _VMETER  ) values(  ? , ? ) ";      
        $result = $this->db->rawQuery( $sql , array($meter, $virtual  ));
    }

     
    function save ( $meter , $virtual ){
		log_message("save vmpp  $meter , $virtual   ");
		
        $find = $this->getVmpp($meter, $virtual);
        if($find){
            return $find;
        }
        $this->addVmpp( $meter , $virtual);
        return $this->getVmpp($meter, $virtual);
    }
     
};


 

class MeterDao extends BaseDao{

  
    function __construct(){
        parent::__construct();
    }

    function removeMeter( $code ){
		log_message("removeMeter $code   ");
		
        if( $code == null )
        {
            throw new Exception('meter empty');
        }

        $sql = "DELETE FROM DATA_METER WHERE _METER='$code'";      
        $result = $this->db->rawQueryOne( $sql ); 
        return $result ;
    }

    function getMeter( $code ){
        if( $code == null )
        {
            throw new Exception('meter empty');
        }

        $sql = "SELECT * FROM DATA_METER WHERE _METER='$code'";      
        $result = $this->db->rawQueryOne( $sql ); 
        return $result ;
    }

    function addMeter( $code , $name , $type, $user , $tags ){
		
		log_message("add meter $code ,  $name  , $type , $user , $tags ");
		
        $sql = "INSERT INTO DATA_METER( _METER , _NAME , _METERTYPE , _USERID , _TAGS ,_UPDATED , _SYNC ) values(  ? , ? , ? , ? , ? , now() , null) ";      
        $result = $this->db->rawQuery( $sql , array($code, $name, $type, $user , $tags ));
    }

    function addOrUpdate( $code , $name , $type, $user, $tags ){
        $find = $this->getMeter($code);
        if($find!=null){
            $this->update1( $code , $name , $type, $user , $tags  );
        }else {
            $this->addMeter($code,$name, $type, $user, $tags);
        }
        return  $this->getMeter($code);
    }
 
    function update1( $meter , $name , $type, $user  , $tags  ){
		log_message("update meter info name $meter ,  $name  , $type , $user , $tags ");
        if( $meter == null )
        {
            throw new Exception('meter empty');
        }

        
        $sql = "UPDATE DATA_METER set _NAME = ? , _USERID=? , _METERTYPE=? , _TAGS=? ,_UPDATED = now() , _SYNC = NULL where _METER=?"; 
        
        $result = $this->db->rawQuery( $sql , array($name, $user, $type, $tags, $meter));
        return $result;
    }
     

    // 更新   name
    function updateName( $meter , $name  ){
		
		log_message("update meter name $meter ,  $name   ");
		
        if( $meter == null )
        {
            throw new Exception('meter empty');
        }

        
        $sql = "UPDATE DATA_METER set _NAME = '$name'  ,_UPDATED = now() , _SYNC=null where _METER='$meter'"; 
        
        $result = $this->db->rawQuery( $sql );
        return $result;
    }     
    /**
     * 
     */
    function getMeters( $q , $user  , $type , $begin , $size ){

  
   
        $sql = "SELECT m.* , u._NAME AS '_USER' FROM DATA_METER m LEFT JOIN DATA_UNIT u ON m._USERID = u._USERID "; 
 

        $hasQ = false;

        if( $q || $user || $type )
        {
            $sql = $sql . " WHERE " ; 
        }
        if($q != ''){
            $sql = $sql . " (( m._METER LIKE '%$q%' ) OR (m._NAME LIKE '%$q%' )) " ; 
            $hasQ = true;
        }

        if( $user != ''){
            if( $hasQ ) {
                $sql = $sql . " and " ; 
            }
            $sql = $sql . "  u._USERID = '$user' " ; 
        }

        if( $type != ''){
            if( $hasQ ) {
                $sql = $sql . " and " ; 
            }
            if( $type == 'V')
                $sql = $sql . "  m._METERTYPE = '$type' " ; 
            else 
            {
                $sql = $sql . "  ( m._METERTYPE = '$type'  or m._METERTYPE is null) " ; 
            }
        }
        if($size !=null && $size > 0 ){
            $sql = $sql . " LIMIT $begin , $size ";
        }

        // print($sql);

        $result = $this->db->rawQuery( $sql );
        return $result;
    }
 
    // 更新 meter 对应关系
    function update($meter , $user ){
		
		log_message("update Meter User  $meter ,  $user   ");
		
        if( $meter == null )
        {
            throw new Exception('meter empty');
        }


        //生成一个连接
 
        $sql = ""; 

        if( $user == null ){
            $sql = "UPDATE DATA_METER set _USERID = null ,_UPDATED = now() , _SYNC = NULL where _METER='$meter' "; 
        }else {
            $sql = "UPDATE DATA_METER set _USERID = '$user' ,_UPDATED = now() , _SYNC = NULL where _METER='$meter'"; 
        }

        // echo $sql;

        // execute query:
        $result = $this->db->rawQuery( $sql );
        return $result;
    }
     
    // 更新 meter 对应关系
    function updateMeterByUser( $user ){

        if( $user == null )
        {
            throw new Exception('user empty');
        }
		log_message("updateMeterByUser   $user   ");

        //生成一个连接
 
        $sql = ""; 

        
        $sql = "UPDATE DATA_METER set _USERID = null ,_UPDATED = now() , _SYNC = NULL where _USERID='$user' "; 
        
        $result = $this->db->rawQuery( $sql );
        return $result;
    }
     
};

 

class UserDao extends BaseDao{

  
    function __construct(){
        parent::__construct();
    }

    function remove( $user ){
        if( $user == null )
        {
            throw new Exception('user empty');
        }
		
        $sql = "DELETE FROM DATA_UNIT WHERE _USERID='$user'";      
        $result = $this->db->rawQueryOne( $sql ); 
		log_message("Remove User $user , $sql , $result ");
        return $result ;
    }

    function enableUser($user , $enable){
        if( !$user  )
        {
            throw new Exception('user empty');
        }

        log_message("Ennable User $user ->  $enable ");
        $sql = "UPDATE DATA_UNIT set _ENABLE =  $enable ,_UPDATED = now() , _SYNC=null where _USERID='$user'"; 
        
        $result = $this->db->rawQuery( $sql );
        return $result;
    }

    /**
     * 
     */
    function getUsers( $q ){

        $sql = "SELECT * FROM DATA_UNIT u";

        if($q != ''){
            $sql = $sql . " WHERE  ( u._USERID LIKE '%$q%' ) OR (u._NAME LIKE '%$q%' )" ;
        }
        $result = $this->db->rawQuery( $sql );
        return $result;
        
    }
    function getUser( $code ){
        if( $code == null )
        {
            throw new Exception('userid empty');
        }

        $sql = "SELECT * FROM DATA_UNIT WHERE _USERID='$code'";      
        $result = $this->db->rawQueryOne( $sql ); 
        return $result ;
    }

    function addUser( $code , $name , $enable,  $tags ){
		
		log_message("Add User   $code  $name $enable $tags ");
		
        $sql = "INSERT INTO DATA_UNIT( _USERID , _NAME ,_TAGS , _ENABLE, _UPDATED ) values( '$code' , '$name' , '$tags' , $enable , now() ) ";      
        $result = $this->db->rawQuery( $sql );
    }
	
	// add or update user ;
    function addOrUpdate( $code , $name , $enable, $tags  ){
        $find = $this->getUser($code);
        if($find!=null){
            $this->updateUser( $code , $name ,$enable, $tags);
        }else {
            $this->addUser($code,$name, $enable,  $tags);
        }
    }   
    
    // 更新   name
    function updateUser($code , $name ,$enable, $tags ){

        if( $code == null )
        {
            throw new Exception('code empty');
        }
	
        log_message("Update User   $code  $name $enable $tags ");
        $sql = "UPDATE DATA_UNIT set _NAME = '$name' , _ENABLE=$enable, _TAGS = '$tags',_UPDATED = now() , _SYNC=null where _USERID='$code'"; 
        
        $result = $this->db->rawQuery( $sql );
        return $result;
    }     

};




// 
/**
 * 用户认证
 */
class AuthDao extends BaseDao{

  
    function __construct(){
        parent::__construct();
    }


    function auth( $user , $pass ){

		log_message("auth   $user  $pass ");
	
        $find = $this->getUser($user);
        if(!$find )
            throw new Exception("Not Found $user", 1);
            
		
        if( $find['PASSWORD'] == $pass )
            return true;
        $sha1pass = sha1($pass);
        if( $find['PASSWORD'] == $sha1pass )
            return true;

        return false;

    }
 
    function savePasswd ( $user , $pass ){
		
		log_message("savePasswd   $user  $pass ");
		
        $find = $this->getUser($user);
        if(!$find )
            throw new Exception("Not Found $user", 1);

        $sha1pass = sha1($pass);
        $sql = "UPDATE USER SET PASSWORD=? WHERE NAME=?";                

        $result = $this->db->rawQueryOne( $sql , array( $sha1pass , $user )); 
        // print("result  is $result <br/>");
        return $result ;

    }
 
    function getUser( $user ){
        if( $user == null )
        {
            throw new Exception('user empty');
        }

        $sql = "SELECT * FROM USER WHERE NAME=?";   
       // print("sql  is $sql <br/>");
        $result = $this->db->rawQueryOne( $sql , array($user)); 
        // print("result  is $result <br/>");
        return $result ;
    }

       

};


class MeterDataDao extends BaseDao{

  
    function __construct(){
        parent::__construct();
    }

 
 
   
    function getData(  $meter = null, $order=null , $begin =0 , $size = 100 ){
        

        $sql = "SELECT * FROM DATA_READING ";   
        
        if( $meter !=null  ){
            $sql = $sql . " WHERE  _METER='$meter' " ;
        }
        if( $order !=null  ){
            $asc = " ASC " ;
            if(  $order[0] == '-')
            {
                $asc=" DESC ";
                $order = substr( $order , 1);
            }
            $sql = $sql . " ORDER BY $order $asc " ;
        }
        if($size !=null && $size > 0 ){
            $sql = $sql . " LIMIT $begin , $size ";
        }


        $result = $this->db->rawQuery ( $sql ,  null ); 
        // print("result  is $result <br/>");
        return $result ;
    }

    function getOne( $meter , $time ){
        $sql= "select * from DATA_READING where _METER='$meter' and _UPDATED='$time'";
        $result = $this->db->rawQueryOne( $sql , null); 
        return $result;
    }
    function remove( $meter  ){
        $sql = '';
        if($meter)
            $sql= "delete from DATA_READING where _METER='$meter'";
        else
            $sql= "delete from DATA_READING ";

        $result = $this->db->rawQuery( $sql , null); 
        return $result;
    }

    function insert(  $meter , $time, $value ){
        
        $time1 = $time->format("Y-m-d H:i:s");

        $result = $this->getOne($meter, $time1);
        if( $result ){
            // update 
            $id = $result['_ID'];
            $sql = "UPDATE DATA_READING SET _VALUE=$value, _RECV=now() where _ID=$id";
            $result1 = $this->db->rawQuery ( $sql ,  null ); 
            return $result1 ;    
        }

        $sql = "INSERT INTO DATA_READING( _METER, _UPDATED , _VALUE, _RECV ) VALUES( '$meter', '$time1', $value, NOW()) ";   
        $result = $this->db->rawQuery ( $sql ,  null ); 
        
        return $result ;
    }

            

};


class UserDataDao extends BaseDao{

  
    function __construct(){
        parent::__construct();
    }

 
 
   
    function getData(  $user = null,  $date=null , $order=null , $begin =0 , $size = 100 ){
        
        $binds  = array();
        $params = array();

        if($user){

            $params['_DEVID'] = '=?';
            $binds []= $user;
        }
    
        if($date){
            $params['_DATE'] = '=?';
            $binds []= $date;
        }


        $sql = BaseDao::bindParams( "DATA_ENERGY" , null , $params ,  $order , $begin , $size );
    
       // print($sql);

        $result = $this->db->rawQuery ( $sql ,  count($binds) > 0 ? $binds : null ); 
        // print("result  is $result <br/>");
        return $result ;
    }

    function getOne( $type, $date, $user , $time ){
        $sql= "select * from DATA_ENERGY where _TYPE='$type' and _DATE='$date'  and _DEVID='$user' and _END='$time'";
        $result = $this->db->rawQueryOne( $sql , null); 
        return $result;
    }    

    function insert(  $user , $date , $time, $value ){
        
        $time1 = $time->format("Y-m-d H:i:s");

        $result = $this->getOne( "U" , $date, $user, $time1);
        if( $result ){
            // update 
            $id = $result['_ID'];
            $sql = "UPDATE DATA_ENERGY SET _VALUE=$value, _TIMESTAMP=now() where _ID=$id";
            $result1 = $this->db->rawQuery ( $sql ,  null ); 
            return $result1 ;    
        }

        $sql = "INSERT INTO DATA_ENERGY( _DEVID, _TYPE , _DATE , _END , _VALUE, _TIMESTAMP ) VALUES( '$user', 'U' ,  '$date' , '$time1', $value, NOW()) ";   
        $result = $this->db->rawQuery ( $sql ,  null ); 
        
        return $result ;
    }   

};



class ExcelUtils{
    static $init = false;
    function __construct(){
        // include_once('./Excel/PHPExcel.php');
             
    }

    static function parseHeaders( $header , $names ){

        // print('dump header<br/>');
        // var_dump( $header);

         
        // print('dump names ' . implode(',', $names)  . '<br/>');

        $result = array();
        $size = count($header);
        for( $i = 0 ; $i< count($header) ; $i++){
            $val = trim($header[$i]);

            if($val){
                $result[$val] = $i;
            }
            //     $pos = ExcelUtils::findPos($names, $val);
            //     if($pos >= 0){ 
            //         // print(' NAME $val pos is $pos<br/>\n');
            //         $result[$val] = $i;
            //     }else {
            //         $result[$val] = $i;
            //     }          
            // }


        }
        // var_dump( $result);
        // print(  '<br/>\n');

        return $result;
    }

    static function findPos( $names , $name ){
        for( $i = 0 ; $i< count($names) ; $i++){
            $val = $names[$i];
            
            if( $val  == $name ){
                return $i;
            }
        }
        return -1;
    }

    static public function parseValues ( $lines , $names )
    {
        // print('dump lines<br/>');
        // var_dump( $lines);
        // 
        //print('parseValues:: dump names ' . implode(',', $names)  . '<br/>');

        $results = array();
        $size = count($lines);
        $header = $lines[0];
        // print("header : ");
        // var_dump( $header);

        $positions = ExcelUtils::parseHeaders($header, $names);
 // var_dump( $positions);
        for ($i = 1 ; $i < $size; $i++ ) {
            $row = $lines[$i];
            $vars = array();
            foreach ($positions as $name => $pos) {
                $val = $row[$pos];
                $vars[$name] = $val;
            }
            // var_dump( $vars);
            // print( '<br/>\n');
            // $results
            array_push($results,$vars);
        }

        return $results;
    }

    static public function read( $filename,   $names , $sheet=null,  $encode='utf-8' ){

        if( !self::$init) {
            // realpath(dirname(__FILE__));
            require_once (realpath(dirname(__FILE__)) . "/../utils/excels/PHPExcel/IOFactory.php");   
            self::$init = true;
        }

        
        // $objReader = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel = PHPExcel_IOFactory::load($filename);
        // $objReader->setReadDataOnly(true);
        // $objPHPExcel = $objReader->load($filename);
        $objWorksheet = null;

        if( $sheet ){
            $objWorksheet = $objPHPExcel->getSheetByName($sheet);
            if(! $objWorksheet)
                throw new Exception("Can't find sheet by name : ". $sheet);
        }else{
            $objWorksheet = $objPHPExcel->getActiveSheet();    
        }
        
        $highestRow = $objWorksheet->getHighestRow(); 
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $excelData = array();

        for ($row = 1; $row <= $highestRow; $row++){
            $rowdata = array();
            for ($col = 0; $col < $highestColumnIndex; $col++){
                // getCalculatedValue 
                $cell = $objWorksheet->getCellByColumnAndRow($col, $row);
                $rowdata[] =(string)$cell->getCalculatedValue();
            }
            // var_dump($rowdata);
            $excelData[$row-1] = $rowdata;
        }
        return ExcelUtils::parseValues( $excelData , $names) ;
    }
}

 


?> 